﻿IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_CalculaAcciones]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_CalculaAcciones];
GO

CREATE PROCEDURE [proc_CalculaAcciones]
(
	@promocionId int
)
AS
BEGIN
	SET NOCOUNT ON	

	DECLARE @tmp TABLE
      (   Id				INT PRIMARY KEY,
		  MontoFactura		MONEY,
          MontoPromocion	MONEY,
		  AccionesPromocion	INT,
		  ParticipanteId	INT)

	DECLARE @numeroAccion		AS INT,
			@MontoFactura		AS MONEY,	
			@MontoPromocion		AS MONEY,
			@AccionesPromocion	AS INT,
			@ParticipanteId		AS INT,
			@nextRowId			AS INT,
			@currentRowId		AS INT,
			@numAcciones		AS INT,
			@i					AS INT
	
	-- Agrega nuevos participantes
	INSERT INTO Participante (Cedula)
	SELECT DISTINCT Cedula
	FROM CargaExcel
	WHERE PromocionId = @promocionId
	AND Cedula NOT IN (SELECT Cedula FROM Participante)
	AND Cedula IN (SELECT DISTINCT Cedula FROM CargaExcel)	
	
	UPDATE Participante
	SET Nombre = c.Nombre,
		Telefono = c.Telefono,
		Empresa = c.Empresa
	FROM CargaExcel c
	WHERE Participante.Cedula = c.Cedula

	-- Genera numero de accion inicial
	SELECT @numeroAccion = ISNULL(MAX(NumeroAccion),0)+1
	FROM Accion

	-- Llena tabla temporal con datos de carga y promocion
	INSERT INTO @tmp (Id, MontoFactura, MontoPromocion, AccionesPromocion, ParticipanteId)
	SELECT c.Id, c.MontoFactura, pr.Monto, pr.CantidadAcciones, pa.Id
	FROM CargaExcel c 
	INNER JOIN Participante pa ON c.cedula = pa.cedula
	INNER JOIN Promocion pr ON c.promocionId = pr.Id
	WHERE PromocionId = @promocionId

	SELECT  @nextRowId = MIN(Id)
	FROM    @tmp

	SELECT  @currentRowId = Id, 
			@MontoFactura = MontoFactura, 
			@MontoPromocion = MontoPromocion,
			@AccionesPromocion = AccionesPromocion, 
			@ParticipanteId = ParticipanteId
	FROM    @tmp
	WHERE   Id = @nextRowId    
	-- Ciclo que recorre @tmp  (sin cursores)
	WHILE 1=1
	BEGIN			
		-- Calcula cantidad de acciones e ingresa c/u a la tabla de acciones
		SET @numAcciones = FLOOR(@MontoFactura/@MontoPromocion)*@AccionesPromocion
		SET @i = 1	
		WHILE (@i <= @numAcciones)
		BEGIN		
			INSERT INTO Accion (NumeroAccion, PromocionId, ParticipanteId, Ganador)
			VALUES (@numeroAccion, @promocionId, @ParticipanteId, 0)
			SELECT @i = @i + 1, @numeroAccion = @numeroAccion + 1
		END
		
		SET @nextRowId = NULL       
		SELECT @nextRowId = MIN(Id)
		FROM @tmp
		WHERE Id > @currentRowId 
		IF ISNULL(@nextRowId,0) = 0 BREAK

		SELECT  @currentRowId = Id, 
				@MontoFactura = MontoFactura, 
				@MontoPromocion = MontoPromocion,
				@AccionesPromocion = AccionesPromocion, 
				@ParticipanteId = ParticipanteId
		FROM    @tmp
		WHERE   Id = @nextRowId   
	END	
END
GO
